mysql数据库设计规范

数据库设计规范

数据库命名规范

规范	备注
数据库对象名称统一使用小写字母并用下划线分割	eg：mkt_tool
数据库对象名称禁止使用MySQL保留关键字	eg:password,from等
数据库对象名称做到见名思义，最长不超过32个字符	尽可能的精简、明确
临时表以tmp为前缀，日期为后缀	eg：mp_user_20180921
备份表以bak为前缀，日期为后缀	eg：bak_user_20180921
所有存储相同数据的列名和列类型必须一致	eg：a表的uid与b表的uid数据类型与列类型一致

数据库基本设计规范

规范	备注
所有数据表使用innodb作为存储引擎	5.6以后作为默认引擎，支持事务、行级锁、更好的恢复性，高并发下性能更好
数据库和表的字符集统一使用UTF8	主要是因为避免由于字符集转换产生的乱码，其次utf8包含的字符更多，一个汉字占3个字节
数据表和字段需要添加注释	后续维护方便
单表数据量控制在500万以内	单表存储的数据量大小限制取决于存储设置和文件系统
尽量做到冷热数据分离，减小表的宽度	避免查询无用的数据
禁止在表中预留字段，禁止在数据库中存储图片及文件等二进制数据	按需增加，避免表数据快速增长
禁止在线上做数据库压力测试
禁止开发环境直接连接生产环境数据库	避免数据脏乱

数据库索引规范

规范	备注
限制每张表索引的数量，单表索引不超过5个	索引不是越多越好，增加查询效率的同时，会降低更新的效率
每个innodb表必须有一个主键	不使用频繁更新的键，不使用hash，字符串，MD5作为主键，优先选取自动增长的列作为主键
如何选择联合索引的顺序	区分度最高的放在索引的最左侧；字段长度最小的放在最左侧；使用度最频繁的列放在最左侧
避免建立冗余度和重复列索引增加了优化器生成查询计划的时间	对于冗余索引并不会提高索引的性能，反而影响查询计划的生成
对于频繁的查询优先考虑使用覆盖索引	避免Innodb进行索引的二次查找
尽量避免使用外键	外键用于保证参照完整性，建议在业务端实现外键的约束，因为每次写操作都需要检测外键约束从而降低性能。

数据库字段规范

规范	备注
优先选择存储需要的最小的数据类型	int类型相比字符串类型存储空间更小，优先使用无符号的整型存储
避免使用text、bolb、enum类型	使用text类型的时候注意只能使用前缀索引，不能有默认值
尽可能的定义为NOT NULL	索引NULL需要额外的空间来保存,进行比较和计算时需要对NULL进行特殊处理
避免使用字符串存储日期型的数据,使用TIMESTAMP或DATETIME存储	1、无法使用日期函数进行比较和计算；2、用字符串存储需要占用更多的空间
使用decimal类型存储金额类数据	1、decimal在计算时不丢失精度，占用的空间由定义的宽度决定；2、可用于存储比bigint更大的数据

数据库SQL开发规范

规范	备注
使用预编译进行数据库操作	1、一次解析，多次使用重复使用执行计划；2、避免动态SQL带来的SQL注入；
避免数据类型的隐式转换	隐式转换、会导致索引失效 eg：select * from test where id = '1'
充分利用已经存在的索引	1、避免使用双%的查询，使用后%进行替换；2、一个SQL只能利用到复合索引中的一列进行范围查询；3、使用left join或not exists来优化not in操作
禁止使用select * 查询	1、消耗更多的CPU和io资源；2、无法使用到覆盖索引；
禁止使用不含字段列表的INSERT语句	明确指定insert的字段
避免使用子查询，可以把子查询优化为join操作	1、子查询的结果集无法使用到索引；2、子查询产生临时表操作，数据量大会影响效率；3、产生的临时表消耗过多的CPU和IO
避免使用JOIN关联太多的表	1、每join一个表会占用一部分内存(join_buffer_size)；2、会产生临时表操作，影响查询效率；3、MySQL允许关联61个表，最多不超过5个
减少同数据库交互的次数	1、数据库适合批量处理，避免多次请求数据库；2、合并多个相同的操作到一起，如一次修改多个字段
使用in代替on	in的值不超过500个,in可以有效的利用索引的
禁止使用order by rand()排序	order by rand()会把所有符合条件的数据装到内存中排序获取
where从句中禁止对列进行函数转换和计算	对列进行函数转换会导致无法使用到索引 eg:where date(time) = '20180123'
明显不会有重复值的情况下使用UNION ALL	UNION会把数据放到临时表进行去重
拆分复杂的大SQL为多个小SQL	MySQL一个SQL只能使用一个CPU计算，SQL拆分后可以通过并行执行提高效率

数据库操作行为规范

规范	备注
超过100万行的数据批量写操作，需要分批多次操作	1、大批量的写操作，容易造成主从延迟 2、产生大量的日志 3、避免产生大事务操作
大表结构修改	直接修改大表容易进行锁表，使用pt-online-schema-change修改表结构
禁止为程序使用的账户授予root权限，遵循权限最小原则	程序使用的账户不准有drop权限，程序使用的账户只能连接到一个数据库